<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@ page import="java.sql.*,java.util.*,java.text.*,java.text.SimpleDateFormat" %>
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Transaction Log</title>
</head>
<body>
<h2 align="center">Transaction Log</h2>
<table border=2 align="center">
<tr style="background-color:#4b545f"><th><font color=white>Voucher Type</font></th><th><font color=white>Transaction Log Details</font></th><th><font color=white>Amount</font></th><th><font color=white>Date and Time</font></th></tr>
<%
DBConnection dbc=new DBConnection();
Connection con=dbc.getNewConnection();
Statement st = null;
ResultSet rs = null;
String name1="",date="",dtecreate="",intime="";

double tot=0.0,tot1=0.0;
	System.out.println("MySQL Connect Example.");
	

	  java.util.Date now = new java.util.Date();
	  String DATE_FORMAT = "yyyy-MM-dd";
	  SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
	  Calendar calendar = Calendar.getInstance();
  String strDateNew = sdf.format(now) ;
  calendar.setTime(sdf.parse(strDateNew));
  calendar.add(Calendar.DATE,-10); 
  String dte = sdf.format(calendar.getTime());

  
  calendar.setTime(sdf.parse(strDateNew));
  calendar.add(Calendar.DATE,1); 
  String dte1 = sdf.format(calendar.getTime());
  

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select scname,stota,datecreated,innum from salesvoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  
	  }
	 String in=rs.getString(4);
	%>


<tr><td>Sales Voucher</td><td>Sales Invoice Booked of <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Sales"> <%=name1 %></a></td><td> <%=tot %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>



<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select scname,stota,dateedited,innum from salesvoucher2 where dateedited between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  
	  }
	 String in=rs.getString(4);
	%>


<tr><td>Sales Voucher</td><td>Sales Invoice Booked of <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Sales"> <%=name1 %></a> Updated</td><td> <%=tot %> </td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>




<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select pvsname,pvtota,datecreated,pvinno from purchasevoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot1=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(2)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	String in=rs.getString(4);
	%>


<tr><td>Purchase Voucher</td><td>Purchase Invoice Booked of  <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Purchase"> <%=name1 %> </a></td><td> <%=tot1 %></td><td><%=intime %></td></tr>

<%}} 
catch(Exception e) {} %>

<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select pvsname,pvtota,dateedited,pvinno from purchasevoucher2 where dateedited between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot1=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(2)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	String in=rs.getString(4);
	%>


<tr><td>Purchase Voucher</td><td>Purchase Invoice Booked of  <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Purchase"> <%=name1 %></a> updated </td><td> <%=tot1 %></td><td><%=intime %></td></tr>

<%}} 
catch(Exception e) {} %>

<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select raccname,bname,ramount,datecreated from receiptsvoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
 String	name2=rs.getString(2);
	tot1=rs.getDouble(3);
	if((rs.getString(4)!=null && rs.getString(4)!=""))
	  {
	  dtecreate = rs.getString(4).toString().substring(0,rs.getString(4).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Receipt Voucher</td><td>Receipt from  <a href="#"> <%=name1 %> </a>from <%=name2 %></td><td> <%=tot1 %></td><td><%=intime %></td></tr>

<%}} 
catch(Exception e) {} %>

<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select pyaccname,pyamount,datecreated from paymentvoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
 
	tot1=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Payment Voucher</td><td>Payment to  <a href="#"> <%=name1 %> </a></td><td> <%=tot1 %></td><td><%=intime %></td></tr>

<%}} 
catch(Exception e) {} %>



<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select pyaccname,pyamount,datecreated from paymentvoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
   double amt=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(2)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Payment Voucher</td><td>Payment to  <a href="#"><%=name1 %></a></td><td> <%=amt %></td><td><%=intime %></td></tr>




<%}} 
catch(Exception e) {} %>


<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select jaccdeb,jacccred,jamount2,datecreated from journalvoucher2 where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	String name2=rs.getString(2);
	double amt=rs.getDouble(3);
	if((rs.getString(4)!=null && rs.getString(4)!=""))
	  {
	  dtecreate = rs.getString(4).toString().substring(0,rs.getString(4).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Journal Voucher</td><td>Debited <a href="#"><%=name1 %></a> and Credited Investment in <a href="#"><%=name2 %></a> Account</td><td> <%=amt %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>


<%

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select dbsname,dbtotal,datecreated,dbinno from debitnote where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	
	String in=rs.getString(4);
	%>


<tr><td>Debit Note Voucher</td><td>Debit Note Booked of <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Debit"> <%=name1 %></a></td><td> <%=tot %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>


<%

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select dbsname,dbtotal,dateedited from debitnote where dateedited between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Debit Note Voucher</td><td>Debit Note Booked of <a href="#"> <%=name1 %></a> updated</td><td> <%=tot %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>


<%

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select cdcname,cdtotal,datecreated,cdinno from creditnote where datecreated between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	String in=rs.getString(4);
	%>


<tr><td>Credit Note Voucher</td><td>Credit Note Booked of <a href="http://localhost:8081/boa/LedgerReportServlet?vouchernumber=<%=in%>&group=Credit"> <%=name1 %></a></td><td> <%=tot %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>


<%

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select cdcname,cdtotal,dateedited from creditnote where dateedited between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	tot=rs.getDouble(2);
	if((rs.getString(3)!=null && rs.getString(3)!=""))
	  {
	  dtecreate = rs.getString(3).toString().substring(0,rs.getString(3).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Credit Note Voucher</td><td>Credit Note Booked of <a href="#"> <%=name1 %></a> Updated</td><td> <%=tot %></td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>

</table>

<br><br>

<center><a href="HomePage.jsp">Back to Home Page</a></center>
</body>
</html>